Imports MyClasses
Imports System.Data.SqlClient
Imports System.Net.Dns
Imports System.Configuration
Imports System.Windows.Forms

Public Class DGeneral
    Public Enum DocTypes
        CASH_MEMO = 1
        CHALLAN = 2
        INVOICE = 3
        APPROVAL = 4
        QUOTATION = 5
        CASH_MEMO_RETRUN = 6
        INVOICE_RETURN = 7
        APPROVAL_RETURN = 8
        PURCHASE_ORDER = 9
        PURCHASE_GRN = 10
        PURCHASE_INVOICE = 11
        PURCHASE_INVOICE_RETURN = 12
        APPROVAL_UPDATE = 13
        CHALLAN_UPDATE = 14
    End Enum
    Public Shared ReadOnly Property OtherChargTAx() As Double
        Get
            Return 112.5 / 100
        End Get
    End Property
    Public Enum USER_TYPE
        ADMIN = 1
        SUPER_USER = 2
        USER = 3
    End Enum
    Public Shared Sub ErrorHandler(ByVal ErrDescr As String, ByVal ProjectName As String, ByVal FormName As String, ByVal FuncOrSubName As String, Optional ByVal Sql As String = "")
        Dim StrSql As String
        Dim param(7) As SqlParameter
        Try

            StrSql = "INSERT INTO tErrorDet (ProjectName,ClassName,FunctSubName,Descr,status,CompName,LU,LD)" & _
                     "VALUES(@ProjectName,@ClassName,@FunctSubName," & _
                      "@Descr,@status,@CompName,@LU,@LD)"

            param(0) = New SqlParameter("@ProjectName", ProjectName)
            param(1) = New SqlParameter("@ClassName", FormName)
            param(2) = New SqlParameter("@FunctSubName", FuncOrSubName)
            param(3) = New SqlParameter("@Descr", IIf(Trim(Sql) <> "", ErrDescr & " Query Statement:- " & Sql, ErrDescr))
            param(4) = New SqlParameter("@status", "A")
            param(5) = New SqlParameter("@CompName", GetCompName())
            param(6) = New SqlParameter("@LU", CCommon.GetUserID())
            param(7) = New SqlParameter("@LD", Date.Now())


            DbHelper.ExecuteNonQuery(StrSql, param)

        Catch ex As Exception
            MessageBox.Show("Database connection error", "NP", MessageBoxButtons.OK, MessageBoxIcon.Error)   ' DGeneral.ErrorHandler(ex.Message, "DAL", "DGeneral", "ErrorHandler")
        End Try
    End Sub

    Public Shared Sub ErrorHandler(ByVal ErrDescr As String, ByVal ProjectName As String, ByVal FormName As String, ByVal FuncOrSubName As String, ByRef dbcon As dbAccess, Optional ByVal Sql As String = "")
        Dim StrSql As String
        Dim param(8) As SqlParameter
        Try

            StrSql = "INSERT INTO tErrorDet (SeqErrorNo,ProjectName,ClassName,FunctSubName,Descr,status,CompName,LU,LD)" & _
                     "VALUES(@SeqErrorNo,@ProjectName,@ClassName,@FunctSubName," & _
                      "@Descr,@status,@CompName,@LU,@LD)"

            param(0) = New SqlParameter("@SeqErrorNo", GetSeqNum("ERROR_LOG"))
            param(1) = New SqlParameter("@ProjectName", ProjectName)
            param(2) = New SqlParameter("@ClassName", FormName)
            param(3) = New SqlParameter("@FunctSubName", FuncOrSubName)
            param(4) = New SqlParameter("@Descr", IIf(Trim(Sql) <> "", ErrDescr & " Query Statement:- " & Sql, ErrDescr))
            param(5) = New SqlParameter("@status", "A")
            param(6) = New SqlParameter("@CompName", GetCompName())
            param(7) = New SqlParameter("@LU", CCommon.GetUserID())
            param(8) = New SqlParameter("@LD", Date.Now())


            dbcon.ExecuteNonQuery(StrSql, param)

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DGeneral", "ErrorHandler")
        End Try
    End Sub

    'Public Shared Function GetSeqID(ByVal objDocType As DocTypes) As String
    '    Return DbHelper.ExecuteScalar("SELECT LTRIM(RTRIM(SEQChar)) + RTRIM(LTRIM(CONVERT(CHAR(5),LastID+1))) AS SEQID FROM  mDocNum WHERE  DocType=" & objDocType)
    'End Function
    Public Shared Function GetPrefVal(ByVal PrefName As String) As String
        Dim StrSql As String = ""
        Try
            StrSql = "SELECT PrefValue FROM mPrefs WHERE PrefName='" & PrefName & "' AND PrefStatus = 'A'"
            Return DbHelper.ExecuteScalar(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DTab", "GetControls", StrSql)
            Return Nothing
        End Try
    End Function

    'Public Shared Function GetSeqID(ByVal DocName As String, ByRef dbConnection As dbAccess) As String
    '    Return dbConnection.ExecuteScalar("Select CodeDesc from mmisccd WHERE CodeType='SEQID' AND CodeID='" & DocName & "'")
    'End Function

    Public Shared Function GetSeqNum(ByVal DocName As String, Optional ByVal Update As Boolean = True) As String

        Dim seqNo As Integer
        Dim strSql As String
        Try
            strSql = "SELECT  LastID + ( CASE FirstEntry WHEN 1 THEN DayEndBuffer + 1 WHEN 0 THEN 1 END ) FROM mdocnum WHERE DocType=" & DocName & ""
            If IsNothing(DbHelper.ExecuteScalar(strSql)) Then
                'Dim param(3) As SqlParameter
                'seqNo = 1
                'If Update = True Then
                '    strSql = "INSERT INTO sdocnum(DocType,LastNum,LU,LD) " & _
                '             "VALUES(@DocType,@LastNum,@LU,@LD) "
                '    param(0) = New SqlParameter("@DocType", DocName)
                '    param(1) = New SqlParameter("@LastNum", seqNo)
                '    param(2) = New SqlParameter("@LU", CCommon.GetUserID())
                '    param(3) = New SqlParameter("@LD", Date.Now())

                '    DbHelper.ExecuteNonQuery(strSql, param)
                'End If
            Else
                seqNo = DbHelper.ExecuteScalar(strSql) '+ 1

                If Update = True Then

                    Dim param(1) As SqlParameter
                    strSql = "UPDATE mdocnum SET LastNum=@LastNum  WHERE DocType=@DocType"

                    param(0) = New SqlParameter("@LastNum", seqNo)
                    param(1) = New SqlParameter("@DocType", DocName)
                    DbHelper.ExecuteNonQuery(strSql, param)
                End If
            End If
            Return seqNo
        Catch ex As Exception
            Return 0
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetSeqNum")
        End Try
    End Function
    Public Shared Function GetSeqNum(ByVal DocName As DocTypes, ByRef dbConnection As dbAccess, Optional ByVal Update As Boolean = True) As String

        Dim seqNo As Integer
        Dim strSql As String

        Try
            ''strSql = 
            'If IsNothing(dbConnection.ExecuteScalar(strSql)) Then
            '    Dim param(1) As SqlParameter
            '    seqNo = 1
            '    If Update = True Then
            '        strSql = "INSERT INTO mdocnum(DocType,LastNum,LU,LD) " & _
            '                 "VALUES(@DocType,@LastNum,@LU,@LD) "
            '        param(0) = New SqlParameter("@DocType", DocName)
            '        param(1) = New SqlParameter("@LastNum", seqNo)

            '        dbConnection.ExecuteNonQuery(strSql, param)
            '    End If

            'Else
            seqNo = dbConnection.ExecuteScalar("SELECT  LastID + ( CASE FirstEntry WHEN 1 THEN DayEndBuffer + 1 WHEN 0 THEN 1 END ) FROM mdocnum WHERE DocType=" & DocName & "")

            If Update = True Then

                Dim param(2) As SqlParameter
                strSql = "UPDATE mdocnum SET lastid=@LastNum, FirstEntry=@FirstEntry  WHERE DocType=@DocType"

                param(0) = New SqlParameter("@LastNum", seqNo)
                param(1) = New SqlParameter("@DocType", DocName)
                param(2) = New SqlParameter("@FirstEntry", False)
                dbConnection.ExecuteNonQuery(strSql, param)
            End If
            'End If

            Return seqNo
        Catch ex As Exception
            Return 0
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetSeqNum")
        End Try
    End Function

    'Public Shared Sub SetSeqno(ByVal docName As String)
    '    Dim strSql As String
    '    Try
    '        strSql = "SELECT LastID FROM sdocnum WHERE DocType='" & docName & "'"
    '        If Not IsDBNull(DbHelper.ExecuteScalar(strSql)) Then
    '            strSql = "UPDATE sdocnum SET LastID=" & DbHelper.ExecuteScalar(strSql) - 1 & " WHERE DocType='" & docName & "'"
    '            DbHelper.ExecuteNonQuery(strSql)
    '        End If
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "SetSeqno")
    '    End Try
    'End Sub
    ''                   26/10/2007 DbConection parameter is passed by Chandrakant
    'Public Shared Sub SetSeqno(ByVal docName As String, ByVal seqID As Integer, ByRef dbConnection As dbAccess)
    '    Dim strSql As String
    '    Try
    '        strSql = "UPDATE sdocnum SET LastID=" & seqID & " WHERE DocType='" & docName & "'"
    '        dbConnection.ExecuteNonQuery(strSql)
    '    Catch ex As Exception
    '        DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "SetSeqno")
    '    End Try
    'End Sub
    Public Shared Function GetLabels(ByRef FormName As String) As DataSet
        'Return DbHelper.ExecuteDataSet("SELECT Container,ControlName,ControlCaption,ControlDescription FROM tLabelDet WHERE ProjectName='" & ProjectName & "' AND FormName='" & FormName & "'")
        'Return DbHelper.ExecuteDataSet("SELECT Container,ControlName,ControlCaption,ControlDescription FROM tLabelDet WHERE FormID=(SELECT formid FROM MFORM WHERE formname='" & FormName & "')")
        Return DbHelper.ExecuteDataSet("SELECT Container,ControlName,ControlCaption,ControlDescription FROM tLabelDet WHERE FormName='" & FormName & "'") '(SELECT formid FROM MFORM WHERE formname='" & FormName & "')")

    End Function
    ' 12/4/2007
    Public Shared Function GetToolTip(ByRef FormName As String) As DataSet
        'Return DbHelper.ExecuteDataSet("SELECT FormId,ControlName,ControlToolTip,Description,Container FROM tToolTip WHERE FormID=(SELECT formid FROM MFORM WHERE formname='" & FormName & "')")
        Return DbHelper.ExecuteDataSet("SELECT FormId,ControlName,ControlToolTip,Description,Container FROM tToolTip WHERE FormName='" & FormName & "')") '
    End Function
    Public Shared Function GetCompName() As String
        Return GetHostName()
    End Function

    Public Shared Function GetError(ByVal ErrorNo As Integer) As DataTable
        Dim strSql As String
        Try
            strSql = "SELECT Message,Type FROM tMsgDet WHERE MsgNo=" & ErrorNo.ToString()
            Return DbHelper.ExecuteDataSet(strSql).Tables(0)
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetError")
        End Try
    End Function
    ' 18/10/2007
    Public Shared Function GetTitle() As DataTable
        Dim strTitle As String
        Try
            strTitle = "SELECT KeyValue FROM mUserSettings WHERE KeyType = 'Title'"
            Return DbHelper.ExecuteDataTable(strTitle)
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetTitle")
        End Try
    End Function
    ' 23/10/2007
    Public Shared Function GetNameSeq() As DataTable
        Dim strNmSq As String
        Try
            strNmSq = "SELECT KeyValue FROM mUserSettings WHERE KeyValue in('TFML','TLFM')"
            Return DbHelper.ExecuteDataTable(strNmSq)
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetNameSeq")
        End Try
    End Function
    Public Shared Function GetLocation() As DataTable
        Try
            'Return DbHelper.ExecuteDataSet("SELECT LocationID,LocationCode FROM mLocation")
            Return DbHelper.ExecuteDataTable("SELECT '0' as LocationID, '' as LocationCode FROM mLocation UNION SELECT LocationID,LocationCode FROM mLocation Order By LocationCode")
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetLocation")
        End Try
    End Function

    Public Shared Function GetTaxes() As DataTable
        Try
            Return DbHelper.ExecuteDataTable("SELECT TaxId,TaxName,Amount FROM mTax")
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetTaxes")
        End Try
    End Function

    Public Shared Function GetFirmName() As String
        Try
            Return DbHelper.ExecuteScalar("Select ShortTitle from mFirmInfo")
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetFirmName")
        End Try
    End Function
    Public Shared Function GetFirmFullName() As String
        Try
            Return DbHelper.ExecuteScalar("Select FirmName from mFirmInfo")
        Catch ex As Exception
            Return Nothing
            DGeneral.ErrorHandler(ex.Message, "DAL", "DFirm", "GetFirmName")
        End Try
    End Function
    'Code Start                                     ...20/07/2007
    Public Shared Function CheckSeqID(ByVal SeqID As String, ByVal DocType As String) As Integer
        Dim StrSql As String = ""
        Try
            Select Case DocType
                Case "ITEM"
                    StrSql = "SELECT ItemID FROM mpItem WHERE  SeqItemID = '" & SeqID & "'"
                Case "MFR"
                    StrSql = "SELECT mfctrID FROM mmanufacturer WHERE  SeqmfctrID = '" & SeqID & "'"
                Case "DC"
                    StrSql = "SELECT DCNO FROM tDC WHERE  SeqDCNO = '" & SeqID & "'"
                Case "INVOICE"
                    StrSql = "SELECT InvoiceID FROM tPurchaseInvoice WHERE SeqInvoiceID = '" & SeqID & "'"
                Case "PR"
                    StrSql = "SELECT PurReturnID FROM tPurReturn WHERE  SeqPurReturnID = '" & SeqID & "'"
                Case "SR"
                    StrSql = "SELECT SaleReturnID FROM tSaleReturn WHERE  SeqSaleReturnID = '" & SeqID & "'"
                Case "DCTR"  '...   22/10/2007
                    StrSql = "SELECT DoctorID FROM mpDoctor WHERE  SeqDoctorID = '" & SeqID & "'"
            End Select

            If IsNothing(DbHelper.ExecuteScalar(StrSql)) Then
                Return False
            Else : Return True
            End If

        Catch ex As Exception
            ErrorHandler(ex.Message, "DAL", "DGeneral", "CheckSeqID", StrSql)
            Return 0
        End Try
    End Function

    Public Shared Function GetCounters() As DataTable
        Dim strSql As String = ""
        Try
            strSql = "Select CounterID,Caption,OperatorID,ColorCode,PriceRate from mCounter Where IsVisibile=1"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            ErrorHandler(ex.Message, "DAL", "DGeneral", "CheckSeqID", strSql)
            Return Nothing
        End Try
    End Function
    ' ...                   24/08/2007
    Public Shared Function GetCounter() As DataSet
        Try
            Return DbHelper.ExecuteDataSet("SELECT '0' as CounterID, '' as Caption FROM mPVendor UNION SELECT CounterID,Caption FROM mcounter")
        Catch ex As Exception
            ErrorHandler(ex.Message, "DAL", "DGeneral", "GetCounter")
            Return Nothing
        End Try
    End Function
    ' 22/11/2007
    Public Shared Function GetDatePicker() As DataTable
        Dim strSql As String = ""
        Try
            strSql = "select  fin_start_dt,fin_end_dt from tFinYear where Working_Status='A'"
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            ErrorHandler(ex.Message, "DAL", "DGeneral", "GetDatePicker", strSql)
            Return Nothing
        End Try
    End Function
    '@ on 20071205:Added for Geting Vendor by Company
    Public Shared Function GetVendor(ByVal Company As Integer) As DataTable
        Dim strsql As String = ""
        Try
            strsql = " SELECT '0' AS VendorID, '' AS Name UNION" & _
                    " SELECT Distinct(R.VendorID) as VendorID, V.VendorName as Name" & _
                     " FROM tRelation R, mPVendor V" & _
                     " WHERE R.mfctrID=" & Company & _
                     " AND R.VendorID = V.VendorID"
            Return DbHelper.ExecuteDataTable(strsql)
        Catch ex As Exception
            ErrorHandler(ex.Message, "DAL", "DGeneral", "GetVendor", strsql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetVendorID(ByVal ItemID As Integer, ByVal CLRcode As String) As Integer
        Try
            'Return DbHelper.ExecuteScalar("SELECT top 1 VendorID FROM tStock WHERE ItemID =" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DGeneral", "GetVendorID", "")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetVendorID(ByVal ItemID As Integer, ByVal CLRcode As String, ByRef dbConnection As dbAccess) As DataTable
        Try
            Return dbConnection.ExecuteDataTable("SELECT top 1 VendorID,StockID FROM tStock WHERE ItemID =" & ItemID & " AND CLRcode='" & CLRcode & "'")
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DGeneral", "GetVendorID", "")
            Return Nothing
        End Try
    End Function
    Public Shared Function FirmInfo() As DataTable
        Dim StrSql As String

        Try
            StrSql = "SELECT mCity.Description ,mFirmInfo.FirmName,mCity.PinCode,mAddress.addDesc1,mAddress.phone1" & _
                    " FROM mAddress" & _
                    " INNER JOIN mFirmInfo ON mAddress.addID = mFirmInfo.addID" & _
                    " INNER JOIN mCity ON mAddress.CityCD = mCity.CityCD"

            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DGeneral", "FirmInfo")
            Return Nothing
        End Try
    End Function
    Public Shared Function GetDocumentBillItems(ByVal SaleID As Integer, ByVal strCheckFinal As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "SELECT SEQSALEID,SeqItemID,ItemName,mfctrShortName,CLRcode," & _
                    " CAST(MONTH(D.EXPDATE)AS VARCHAR) + '/' + CAST(YEAR(D.EXPDATE)AS VARCHAR) AS EXPDATE," & _
                    " SchemeID, SchemeItemQty, Qty, D.SalePrice, D.Amount" & _
                    " FROM TSALE Ts" & _
                    " INNER JOIN TSALEDET D ON Ts.SALEID = D.SALEID" & _
                    " INNER JOIN MPITEM I ON I.ITEMID = D.ITEMID" & _
                    " INNER JOIN MMANUFACTURER M ON M.MFCTRID = I.MFCTRID" & _
                    " WHERE "
            If strCheckFinal = "F" Then
                strSql = strSql & "(TS.SALEID =" & SaleID & " AND FinalBillID='F') " & _
                    "OR (TS.SALEID IN (SELECT SALEID FROM TSALE WHERE FinalBillID='" & SaleID & "' ))"
            Else
                strSql = strSql & " TS.SALEID = " & SaleID
            End If



            'strSql = "SELECT SeqItemID,ItemName,mfctrShortName,CLRcode,CAST(MONTH(D.EXPDATE)AS VARCHAR) + '/' + CAST(YEAR(D.EXPDATE)AS VARCHAR) AS EXPDATE," & _
            '         "SchemeID,SchemeItemQty,Qty,D.SalePrice,Amount " & _
            '         "FROM  tSaleDet D LEFT OUTER JOIN mpItem I ON D.ItemID=I.ItemID " & _
            '         "LEFT OUTER JOIN mManufacturer M ON M.mfctrID=I.mfctrID " & _
            '         "WHERE SaleID=" & SaleID
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetDocumentBillItems", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function GetCustomerDoct(ByVal SaleID As Integer, ByVal strCheckFinal As String) As DataTable
        Dim strSql As String = ""
        Try
            strSql = "Select SEQSALEID,Mpc.Fname+' '+Mpc.Mname+' '+Mpc.Lname AS CustomerName, Mpd.Fname+' '+Mpd.Mname+' '+Mpd.Lname AS DoctorName," & _
                    " Discount,GrossAmount,PaidAmount from TSALE Ts" & _
                    " INNER JOIN  mCustomer Mc ON Mc.CustID = Ts.CustID" & _
                    " INNER JOIN  mPerson Mpc ON Mpc.PersonID = Mc.PersonID" & _
                    " INNER JOIN  mPDoctor Md ON Md.DoctorID = Ts.DrID" & _
                    " INNER JOIN  mPerson Mpd ON Mpd.PersonID = Md.PersonID" & _
                    " WHERE"
            If strCheckFinal = "F" Then
                strSql = strSql & "(TS.SALEID =" & SaleID & " AND FinalBillID='F') "
            Else
                strSql = strSql & " TS.SALEID = " & SaleID
            End If
            'strSql = "SELECT SeqItemID,ItemName,mfctrShortName,CLRcode,CAST(MONTH(D.EXPDATE)AS VARCHAR) + '/' + CAST(YEAR(D.EXPDATE)AS VARCHAR) AS EXPDATE," & _
            '         "SchemeID,SchemeItemQty,Qty,D.SalePrice,Amount " & _
            '         "FROM  tSaleDet D LEFT OUTER JOIN mpItem I ON D.ItemID=I.ItemID " & _
            '         "LEFT OUTER JOIN mManufacturer M ON M.mfctrID=I.mfctrID " & _
            '         "WHERE SaleID=" & SaleID
            Return DbHelper.ExecuteDataTable(strSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCustomerDoct", strSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function IsBillConverted(ByVal SALEDID As Integer) As Boolean
        Dim strSql As String = ""
        Try
            strSql = "SELECT ISNULL(FinalBillID,0) FROM TSALE WHERE SaleID=" & SALEDID
            If DbHelper.ExecuteScalar(strSql) > 0 Then
                Return True
            End If
            Return False

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCustomerDoct", strSql)
            Return False
        End Try
    End Function
    Public Shared Function IsBilled(ByVal SALEDID As Integer) As Boolean
        Dim strSql As String = ""
        Try
            strSql = "SELECT ISNULL(Billed,0) FROM TSALE WHERE SaleID=" & SALEDID
            If DbHelper.ExecuteScalar(strSql) > 0 Then
                Return True
            End If
            Return False

        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DSale", "GetCustomerDoct", strSql)
            Return False
        End Try
    End Function
    Public Shared Function CheckFormMode() As DataTable
        Dim StrSql As String

        Try
            StrSql = " SELECT Status, Working_Status FROM tFinYear WHERE FinYrId = " & ConfigurationManager.AppSettings("FinYrId") & " "

            Return DbHelper.ExecuteDataTable(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DGeneral", "CheckFormMode")
            Return Nothing
        End Try
    End Function

    Public Shared Function GetItemList(ByVal intColumnIndex As Integer, ByRef RecordCount As Integer) As SqlDataReader

        Dim StrSql As String = ""
        Try
            StrSql = "SELECT P.[Id] AS ItemID,P.[ProductCode] AS Code,P.[ProductName] AS Name,S.SupplierName AS Company,ISNULL(R.PricePerPack,1) AS Rate " & _
                         " FROM [NGSPaintsDB].[dbo].Product  P LEFT OUTER JOIN [NGSPaintsDB].[dbo].Supplier S ON(P.[SupplierId]=S.[Id]) LEFT OUTER JOIN [NGSPaintsDB].dbo.Rate R ON(P.ID=R.ProductId) " & _
                         " ORDER BY "
            Dim StrRecordCntSql As String = "SELECT Count(ID) FROM [NGSPaintsDB].dbo.Product"

            If intColumnIndex = 1 Then
                StrSql = StrSql & " Code"
            Else : StrSql = StrSql & " Name"
            End If
            Dim param(0) As SqlParameter
            param(0) = New SqlParameter("@Orderby", IIf(intColumnIndex = 1, "Code", "Name"))
            RecordCount = DbHelper.ExecuteScalar(StrRecordCntSql)
            Return DbHelper.ExecuteDataReader(StrSql)
        Catch ex As Exception
            DGeneral.ErrorHandler(ex.Message, "DAL", "DItem", "GetItemList", StrSql)
            Return Nothing
        End Try
    End Function
    Public Shared Function SubstractNGItemStock(objNGItem As CNGItem, ByRef dbConnection As dbAccess) As Integer
        Dim intResult As Integer = 0
        '' update base 
        intResult = dbConnection.ExecuteNonQuery("UPDATE PackingUpdateDetail SET Stock=Stock -" & objNGItem.Quantity.ToString() & " WHERE ID=" + objNGItem.intPackUpdateDtl_PK.ToString())

        If objNGItem.isTinTin Then
            Dim dtbTinters As DataTable = dbConnection.ExecuteDataTable("SELECT * FROM Tinting WHERE ID = " & objNGItem.intTinTinID.ToString())
            If dtbTinters.Rows.Count > 0 Then
                Dim dblValue As Double = 0
                For intcount As Integer = 1 To 24
                    dblValue = dtbTinters.Rows(0).Item("Column" & intcount.ToString())
                    If dblValue > 0 Then

                        intResult = dbConnection.ExecuteNonQuery("UPDATE PackingUpdateDetail SET Stock=Stock -" & (dblValue * objNGItem.Quantity) & " WHERE ID= (SELECT  PackingUpdateDetailsId FROM TintingColumnMapping WHERE ProductID = " + objNGItem.ItemID.ToString() + "  AND MappedColumnName = '" + dtbTinters.Rows(0).Item("Column" & intcount.ToString()) + "')")
                        If intResult = 0 Then
                            Return 0
                        End If
                    End If
                Next
            End If
        End If
        Return intResult
    End Function
    Public Shared Function AddNGItemStock(objNGItem As CNGItem, ByRef dbConnection As dbAccess) As Integer
        If objNGItem.isTinTin Then
        Else
            Dim strSql As String = "UPDATE PackingUpdateDetail SET Stock=Stock +" & objNGItem.Quantity.ToString() & " WHERE ID=" + objNGItem.intPackUpdateDtl_PK.ToString()
            Return dbConnection.ExecuteNonQuery(strSql)
        End If

    End Function
    Public Shared Function Add_GRN_ItemStock(objNGItem As CNGItem, ByRef dbConnection As dbAccess) As Integer
        If objNGItem.isTinTin Then
        Else

            Return dbConnection.ExecuteNonQuery("UPDATE PAckingUpdateDetail SET Stock = Stock +  " & objNGItem.intReceivedQuantity & "  WHERE ID=" + objNGItem.intPackUpdateDtl_PK.ToString())
        End If

    End Function

    Public Shared Function funPriIsEODDone(EOD_Date As Date)
        If DbHelper.ExecuteScalar("SELECT COUNT(ID) FROM tbl_Eod WHERE  EOD_DATE = '" & EOD_Date & "'") > 1 Then
            MessageBox.Show("EOD is already performed!", "NP", MessageBoxButtons.OK, MessageBoxIcon.Error)
            Return True
        Else
            Return False
        End If
    End Function

    Public Shared Function GetNextDocNumber(ByVal iDocType As Integer) As Integer
        Return DbHelper.ExecuteScalar("SELECT ISNULL(CurID,0)as CURID FROM Tbl_EOD_Det WHERE DocType =" & iDocType.ToString() & " AND CurID BETWEEN FirstID AND  LastID")
    End Function
    Public Shared Function UpdateNextDocNumber(objNGItem As CNGItem, ByRef dbConnection As dbAccess) As Integer
        If objNGItem.isTinTin Then
        Else

            Return dbConnection.ExecuteNonQuery("UPDATE PAckingUpdateDetail SET Stock = Stock +  " & objNGItem.intReceivedQuantity & "  WHERE ID=" + objNGItem.intPackUpdateDtl_PK.ToString())
        End If

    End Function
End Class